#! /bin/bash
HIVE_HOME=/usr/bin/hive
Last_DATE=$(date -d "-1 day" +%Y-%m-%d)
${HIVE_HOME} -S -e "
SET hive.auto.convert.join=false;
--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
SET hive.exec.max.dynamic.partitions.pernode=10000;
SET hive.exec.max.dynamic.partitions=10000;
SET hive.exec.max.created.files=150000;
--hive压缩
SET hive.exec.compress.intermediate=true;
SET hive.exec.compress.output=true;
--写入时压缩生效
SET hive.exec.orc.compression.strategy=COMPRESSION;
--矢量优化
SET hive.vectorized.execution.enabled=true;
--关联优化
SET hive.optimize.correlation=true;
--读取零拷贝
SET hive.exec.orc.zerocopy=true;
SET hive.optimize.skewjoin=true;
SET hive.optimize.skewjoin.complietime=true;
SET hive.uptimize.union.remove=true;
SET hive.groupby.skewindata=true;
INSERT INTO zz15_dws_renxinlei.student_attendance PARTITION(yearinfo,monthinfo,dayinfo)
SELECT
   ctud.class_date,
   ctud.class_id,
   sso.student_id,
   if(
       sum(
            if(
                unix_timestamp(sso.signin_time,'yyyy-MM-dd HH:mm:ss') 
                    BETWEEN unix_timestamp(concat(ctud.class_date,' ',ct.morning_begin_time),'yyyy-MM-dd HH:mm:ss') - 40*60 
                        AND unix_timestamp(concat(ctud.class_date,' ',ct.morning_end_time),'yyyy-MM-dd HH:mm:ss')
            ,1,0)
       )>0,--如果大于0，则当天打卡记录中一定有出勤的打卡记录，小于等于0，认为没有出勤
       if(
            sum(
                if(
                    unix_timestamp(sso.signin_time,'yyyy-MM-dd HH:mm:ss') 
                    BETWEEN unix_timestamp(concat(ctud.class_date,' ',ct.morning_begin_time),'yyyy-MM-dd HH:mm:ss') - 40*60 
                        AND unix_timestamp(concat(ctud.class_date,' ',ct.morning_begin_time),'yyyy-MM-dd HH:mm:ss') + 10*60
                ,1,0)
            )>0
       
       ,0,1)--如果打卡时间在开课前40分钟和开课十分钟内算正常出勤0，否则算迟到出勤1
   ,2)AS morning_att,
   if(
       sum(
            if(
                unix_timestamp(sso.signin_time,'yyyy-MM-dd HH:mm:ss') 
                    BETWEEN unix_timestamp(concat(ctud.class_date,' ',ct.afternoon_begin_time),'yyyy-MM-dd HH:mm:ss') - 40*60 
                        AND unix_timestamp(concat(ctud.class_date,' ',ct.afternoon_end_time),'yyyy-MM-dd HH:mm:ss')
            ,1,0)
       )>0,--如果大于0，则当天打卡记录中一定有出勤的打卡记录，小于等于0，认为没有出勤
       if(
            sum(
                if(
                    unix_timestamp(sso.signin_time,'yyyy-MM-dd HH:mm:ss') 
                    BETWEEN unix_timestamp(concat(ctud.class_date,' ',ct.afternoon_begin_time),'yyyy-MM-dd HH:mm:ss') - 40*60 
                        AND unix_timestamp(concat(ctud.class_date,' ',ct.afternoon_begin_time),'yyyy-MM-dd HH:mm:ss') + 10*60
                ,1,0)
            )>0
       
       ,0,1)--如果打卡时间在开课前40分钟和开课十分钟内算正常出勤0，否则算迟到出勤1
   ,2) AS afternoon_att,
   if(
       sum(
            if(
                unix_timestamp(sso.signin_time,'yyyy-MM-dd HH:mm:ss') 
                    BETWEEN unix_timestamp(concat(ctud.class_date,' ',ct.evening_begin_time),'yyyy-MM-dd HH:mm:ss') - 40*60 
                        AND unix_timestamp(concat(ctud.class_date,' ',ct.evening_end_time),'yyyy-MM-dd HH:mm:ss')
            ,1,0)
       )>0,--如果大于0，则当天打卡记录中一定有出勤的打卡记录，小于等于0，认为没有出勤
       if(
            sum(
                if(
                    unix_timestamp(sso.signin_time,'yyyy-MM-dd HH:mm:ss') 
                    BETWEEN unix_timestamp(concat(ctud.class_date,' ',ct.evening_begin_time),'yyyy-MM-dd HH:mm:ss') - 40*60 
                        AND unix_timestamp(concat(ctud.class_date,' ',ct.evening_begin_time),'yyyy-MM-dd HH:mm:ss') + 10*60
                ,1,0)
            )>0
       
       ,0,1)--如果打卡时间在开课前40分钟和开课十分钟内算正常出勤0，否则算迟到出勤1
   ,2) AS evening_att,
   substr(ctud.class_date,1,4) AS yearinfo,
   substr(ctud.class_date,6,2) AS monthinfo,
   substr(ctud.class_date,9,2) AS dayinfo

from (SELECT * FROM itcast_ods.course_table_upload_detail WHERE nvl(content,'')!='' AND content !='开班典礼' AND Last_DATE=${Last_DATE})  ctud
    LEFT JOIN (SELECT * FROM itcast_ods.student_signin_ods WHERE share_state = 1)  sso
on sso.class_id = ctud.class_id
    LEFT JOIN itcast_ods.class_time  ct
on ct.id = sso.time_table_id
GROUP BY ctud.class_date,ctud.class_id,sso.student_id;
SELECT * from zz15_dws_renxinlei.student_attendance;